package DBLayer;

import ModelLayer.SalesOrder;
import ModelLayer.SalesLineItem;
import java.sql.*;
import java.util.ArrayList;

/**
 *
 * @author Gruppe 2
 */
public class DbSalesOrder extends DBExtentions implements IFDBSalesOrder {

    private Connection con;
    private IFDBCustomer dbCus;
    private IFDBSalesLineItem saleItem;
    private IFDBInvoice invoice;
    private ArrayList<String> queryList = new ArrayList<String>();

    public DbSalesOrder()
    {
        con = DbConnection.getInstance().getDBcon();
        dbCus = new DbCustomer();
        saleItem = new DBSalesLineItem();
        invoice = new DBInvoice();
    }

    public ArrayList<SalesOrder> getAllSalesOrders()
    {
        return miscWhere("");
    }

    public SalesOrder searchSalesOrderID(int salesOrderId)
    {
        String wClause = "salesID = " + salesOrderId;
        System.out.println("searchSalesOrderID " + wClause);
        return singleWhere(wClause);
    }

    public int insertSalesOrder(SalesOrder saleOrder)
    {
        queryList.clear();
        int nextSalesID = GetMax.getMaxId("Select max(salesID) from SalesOrder");
        nextSalesID = nextSalesID + 1;
        System.out.println("next sale = " + nextSalesID);


        System.out.println("ID: "+saleOrder.getCustomer().getPersonID());

//                saleOrder.getCustomer().getPersonID()+" "+saleOrder.getDate()+" "+
//                saleOrder.getDiscount()+" "+saleOrder.getDeliveryDate());


        int rc = -1;
        //Transaktion 1 Begins
        String query = "INSERT INTO SalesOrder(salesID, date, amount, deliveryStatus,"
                + " deliveryDate, discount, customerID, InvoiceID)  VALUES('"
                + nextSalesID + "','"
                + saleOrder.getSalesOrderID() + "','"
                + saleOrder.getDate() + "',' "
                //+saleOrder.getAmount() + "',' "
               // + saleOrder.getDeliveryStatus() + "',' "
                + saleOrder.getDeliveryDate() + "',' "
                + saleOrder.getDiscount() + "',' "
                + saleOrder.getCustomer().getPersonID() + "' "
                + nextSalesID +"')";

        System.out.println("insert : " + query);
        queryList.add(query);
        //Transaktion 1 Ends
        rc = rollBack(queryList);
        return rc;
    }

    public int updateSalesOrder(SalesOrder saleOrder)
    {
        queryList.clear();
        int rc = -1;
        String query = "UPDATE SalesOrder SET "
                + "date ='" + saleOrder.getDate() + "', "
                + "amount ='" + saleOrder.getAmount() + "', "
                + "deliveryStatus ='" + saleOrder.getDeliveryStatus() + "', "
                + "deliveryDate ='" + saleOrder.getDeliveryDate() + "', "
                + "discount ='" + saleOrder.getDiscount() + "', "
                + "customerID ='" + saleOrder.getCustomer().getPersonID() + "', "
                + "InvoiceID ='" + saleOrder.getInvoice().getInvoiceNo() + "', "
                + "WHERE salesID = '" + saleOrder.getSalesOrderID() + "'";
        System.out.println("Update query:" + query);

        rc = rollBack(queryList);
        return rc;
    }

    public int deleteSalesOrder(int salesID)
    {
        queryList.clear();
        int rc = -1;
        //Transaktion 1 Begin
        String query = "DELETE FROM SalesOrder WHERE salesID = '"
                + salesID + "'";
        System.out.println("Delete Ref " + query);
        //Transaktion 1 Ends
        rc = rollBack(queryList);
        //Transaktion 2 Begin
        query = "DELETE FROM SalesLineItem WHERE salesID = '"
                + salesID + "'";
        System.out.println("Delete : " + query);
        //Transaktion 2 Ends
        rc = rollBack(queryList);
        return rc;
    }

    private ArrayList<SalesOrder> miscWhere(String wClause)
    {
        ResultSet results;
        ArrayList<SalesOrder> list = new ArrayList<SalesOrder>();
        String query = buildQuery(wClause);
        try
        { // read the employee from the database
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);
            while (results.next())
            {
                SalesOrder orderObj = new SalesOrder();
                orderObj = buildSalesOrder(results);
                list.add(orderObj);
            }//end while
            stmt.close();
        }//slut try
        catch (Exception e)
        {
            System.out.println("Query exception - select: " + e);
            e.printStackTrace();
        }
        return list;
    }

    //Singelwhere is used when we only select one SalesLineItem
    private SalesOrder singleWhere(String wClause)
    {
        ResultSet results;
        SalesOrder saleObj = new SalesOrder();
        String query = buildQuery(wClause);
        System.out.println(query);
        try
        { // read the employee from the database
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            if (results.next())
            {
                saleObj = buildSalesOrder(results);
            }
        }//end try
        catch (Exception e)
        {
            System.out.println("Query exception: " + e);
        }
        return saleObj;
    }

    //method to build the query
    private String buildQuery(String wClause)
    {
        String query = "SELECT * FROM SalesOrder order, Invoice invoice";

        if (wClause.length() > 0)
        {
            query = query + " WHERE invoice.InvoiceID = order.invoiceID "
                    + " " + wClause;
        }
        return query;
    }

    private SalesOrder buildSalesOrder(ResultSet results)
    {
        SalesOrder order = new SalesOrder();
        try
        {
            order.setCustomer(dbCus.searchCustomerId(results.getInt("customerID")));
            order.setInvoice(invoice.searchInvoiceNo(results.getInt("InvoiceID")));
            order.setDate(results.getString("date"));
            order.setDeliveryDate(results.getString("deliveryDate"));
            order.setDeliveryStatus(results.getString("deliveryStatus"));
            order.setDiscount(results.getDouble("discount"));

            double totalAmount = 0;
            for (SalesLineItem item : saleItem.searchSalesLine(results.getInt("salesID")))
            {
                totalAmount += item.getLineTotal();
            }
            order.setAmount(totalAmount);
        }
        catch (Exception e)
        {
            System.out.println("error in building the SalesLineItem object");
        }
        return order;
    }
}
